﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using CommClass;

namespace DMS
{
    public partial class FrmDoImportsurplier : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile;
        Surplier sp = new Surplier(App.Ds);  
        public FrmDoImportsurplier()   
        {
            InitializeComponent();
        }

        private void FrmDoImportSAP_Load(object sender, EventArgs e)
        {

        }
       
    



        private void button1_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }


        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (tbFileName.Text != null && tbFileName.Text.Trim() != "")
            {
                string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';Data Source=" + tbFileName.Text;
                OleDbConnection myOleDbConnection = new OleDbConnection(constr);
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter("select * from [" + cbbSheets.Text + "] order by 供应商名称 ", myOleDbConnection);
                DataTable myDataTable = new DataTable();
                myOleDbDataAdapter.Fill(myDataTable);



                //显示excel数据
                dvgDO.DataSource = myDataTable;
                //dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "] order by 提单号 ").Tables[0];

                this.button3.Enabled = true;
            }
            else
            {
                  MessageBox .Show ( "请选择文件！");
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            listBox1.Items.Add("开始导入数据...");
            ////获取excel数据
            DataTable dtt =(DataTable)dvgDO.DataSource;
            
              //遍历excel数据并上传至数据库
           
            
                //Math.Round(45.367,2)  
                using (SqlConnection conn = new SqlConnection(App.GetSqlConnection()))
                {
                    conn.Open();
                    using (SqlTransaction tran = conn.BeginTransaction()) //开始数据库事务。即创建一个事务对象tran  
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.Connection = conn;
                            cmd.Transaction = tran; //获取或设置将要其执行的事务  
                            try
                            {
                                if(dtt.Rows.Count>0)
                                {
                                   for ( int i = 0;i <dtt.Rows.Count;i++  )
                                  {


                                      //保存主表
                                      SupplySupplier ss = new SupplySupplier();
                                      ss.SupplySupplierName = dtt.Rows[i]["供应商名称"].ToString().Trim();
                                      ss.ShortName = dtt.Rows[i]["供应商名称"].ToString().Trim();


                                      ss.StorageID = sp.GetStorageID(dtt.Rows[i]["越库库位"].ToString().Trim(), conn, cmd);
                                      ss.ShouhuoshuxingID = sp.GetshouhuoshuxingID(dtt.Rows[i]["收货属性"].ToString().Trim(), conn, cmd);
                                      ss.CengjiID = sp.GetcengjiID(dtt.Rows[i]["层级"].ToString().Trim(), conn, cmd);
                                      ss.Dizhi = dtt.Rows[i]["地址"].ToString().Trim();
                                      ss.Hezuoshangpin = dtt.Rows[i]["合作产品"].ToString().Trim();
                                      ss.Lianxifangshi = dtt.Rows[i]["联系方式"].ToString().Trim();
                                      ss.Yinhangzhanghao = dtt.Rows[i]["开户行账号"].ToString().Trim();
                                      ss.Gonghuofanwei = dtt.Rows[i]["供货范围"].ToString().Trim();
                                      ss.Qisongliang = dtt.Rows[i]["起送量"].ToString().Trim();
                                      ss.Tiqianqi = dtt.Rows[i]["提前期"].ToString().Trim();
                                      ss.FukuanID = sp.GetfukuanID(dtt.Rows[i]["付款方式"].ToString().Trim(), conn, cmd);
                                      ss.Zhangqi = dtt.Rows[i]["账期"].ToString().Trim();
                                      ss.SupplySupplierStatusID = sp.GetSupplySupplierStatusID(dtt.Rows[i]["状态"].ToString().Trim(), conn, cmd);
                                      ss.SupplySupplierID = sp.GetSupplySupplierID(dtt.Rows[i]["供应商名称"].ToString().Trim(), conn, cmd);



                                      if (ss.SupplySupplierID>0)
                                      {
                                       
                                          ss.updateSupplySupplier(ss, conn, cmd);
                                      }
                                      else
                                      {
                                          ss.SupplySupplierID = ss.saveSupplySupplier(ss, conn, cmd);
                                          
                                      }

                                   }
                                }
                                   tran.Commit();
                                conn.Close();
                                //MessageBox.Show("上传成功");
                            }
                            catch
                            {

                                tran.Rollback();//如果执行不成功，发送异常，则执行rollback方法，回滚到事务操作开始之前。
                               conn.Close();
                                MessageBox.Show("上传失败");
                           }
                       }
                    }

                }
                 listBox1.Items.Add("数据导入完成！");

         }

        private void button4_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void cbbSheets_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void tbFileName_TextChanged(object sender, EventArgs e)
        {

        }
    }
    
}

